package com.example.homeworkmutualevaluation.Dao;

import com.example.homeworkmutualevaluation.Entity.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.List;

@Repository
public interface StudentDao extends JpaRepository<Student, String> {
    Student findByPhone(String phone);
    @Query(value = "SELECT * FROM student WHERE `phone`=?1 AND `password`=sha1(?2)", nativeQuery = true)
    Student find(String phone, String password);
    @Query(value = "SELECT * FROM student ORDER BY `phone`", nativeQuery = true)
    List<Student> findAllSorted();
    @Transactional
    @Modifying
    @Query(value = "INSERT INTO student(`phone`, `password`) VALUES (?1, sha1(?2))", nativeQuery = true)
    int insert(String phone, String password);
    @Transactional
    @Modifying
    @Query(value = "UPDATE student SET `password`=sha1(?3) WHERE `phone`=?1 AND `password`=sha1(?2)", nativeQuery = true)
    int updatePassword(String phone, String oldPassword, String newPassword);
    @Transactional
    @Modifying
    @Query(value = "UPDATE student SET `password`=sha1(?2) WHERE `phone`=?1", nativeQuery = true)
    int updateByAdmin(String phone, String password);
    @Transactional
    @Modifying
    @Query(value = "UPDATE student SET `name`=?2, `sex`=?3, `birthday`=?4 WHERE `phone`=?1", nativeQuery = true)
    int updateInfo(String phone, String name, String sex, Date birthday);
    @Transactional
    @Modifying
    @Query(value = "UPDATE student SET `bannedtime`=?2 WHERE `phone`=?1", nativeQuery = true)
    int updateBannedtime(String phone, Timestamp bannedtime);
    @Transactional
    @Modifying
    @Query(value = "DELETE FROM student WHERE `phone`=?1", nativeQuery = true)
    int delete(String phone);
    @Query(value = "SELECT student.* FROM student, sc WHERE `cno`=?1 AND `phone`=`sphone` ORDER BY `phone`", nativeQuery = true)
    List<Student> findAllByCno(String cno);
}
